const logger = require('../utils/logger');
const MigrationEngine = require('./MigrationEngine');

/**
 * SchemaEngine
 * 负责管理数据表元数据，支持动态表结构发现和编辑
 */
class SchemaEngine {
    constructor(knex) {
        this.knex = knex;
        this.migrationEngine = new MigrationEngine(knex);
        this.systemTableName = 'sys_schemas';
        // 系统内部表，不在 Schema 管理器中显示
        this.internalTables = ['sys_schemas', 'sqlite_sequence', 'knex_migrations', 'knex_migrations_lock'];
    }

    /**
     * 初始化：确保系统表存在，并同步现有表
     */
    async init() {
        try {
            const exists = await this.knex.schema.hasTable(this.systemTableName);
            if (!exists) {
                await this.knex.schema.createTable(this.systemTableName, (table) => {
                    table.increments('id').primary();
                    table.string('name').unique().notNullable();
                    table.string('display_name').notNullable();
                    table.json('fields').notNullable();
                    table.text('description');
                    table.boolean('is_system').defaultTo(false);
                    table.string('db_type').defaultTo('main');
                    table.timestamp('created_at').defaultTo(this.knex.fn.now());
                    table.timestamp('updated_at').defaultTo(this.knex.fn.now());
                });
                logger.info(`[SchemaEngine] System table ${this.systemTableName} created`);
            } else {
                // 检查并添加缺失的列
                const columns = await this.knex.raw(`PRAGMA table_info('${this.systemTableName}')`);
                const columnNames = columns.map(c => c.name);
                if (!columnNames.includes('db_type')) {
                    await this.knex.schema.alterTable(this.systemTableName, (table) => {
                        table.string('db_type').defaultTo('main');
                    });
                    logger.info(`[SchemaEngine] Added db_type column`);
                }
            }
            await this.syncExistingTables();
        } catch (error) {
            logger.error(`[SchemaEngine] Init failed: ${error.message}`);
            throw error;
        }
    }

    /**
     * 同步数据库中现有的表到 sys_schemas
     */
    async syncExistingTables() {
        try {
            const tables = await this.knex.raw(
                "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
            );
            const tableNames = tables.map(t => t.name).filter(name => !this.internalTables.includes(name));
            logger.info(`[SchemaEngine] Found ${tableNames.length} tables to sync: ${tableNames.join(', ')}`);

            let newCount = 0, updatedCount = 0;
            for (const tableName of tableNames) {
                const existing = await this.knex(this.systemTableName).where('name', tableName).first();
                const displayName = this._generateDisplayName(tableName);

                if (!existing) {
                    const columns = await this.knex.raw(`PRAGMA table_info('${tableName}')`);
                    const fields = columns.map(col => ({
                        name: col.name,
                        type: this._sqliteTypeToFieldType(col.type),
                        displayName: this._generateFieldDisplayName(col.name),
                        required: col.notnull === 1,
                        primaryKey: col.pk === 1
                    })).filter(f => !['id', 'created_at', 'updated_at'].includes(f.name));

                    await this.knex(this.systemTableName).insert({
                        name: tableName,
                        display_name: displayName,
                        fields: JSON.stringify(fields),
                        description: `自动发现的表: ${tableName}`,
                        is_system: true,
                        db_type: 'main'
                    });
                    newCount++;
                    logger.info(`[SchemaEngine] Synced new table: ${tableName}`);
                } else {
                    // 检查是否需要更新显示名或字段显示名
                    let needsUpdate = false;
                    const updates = { updated_at: this.knex.fn.now() };

                    // 1. 检查表显示名
                    if (existing.display_name === tableName || existing.display_name !== displayName) {
                        updates.display_name = displayName;
                        needsUpdate = true;
                    }

                    // 2. 检查字段显示名
                    try {
                        const currentFields = typeof existing.fields === 'string' ? JSON.parse(existing.fields) : existing.fields;
                        let fieldsChanged = false;

                        const updatedFields = currentFields.map(field => {
                            const newDisplayName = this._generateFieldDisplayName(field.name);
                            // 只有当当前显示名等于字段名（未修改）且新显示名不同时才更新
                            if (field.displayName === field.name && newDisplayName !== field.name) {
                                fieldsChanged = true;
                                return { ...field, displayName: newDisplayName };
                            }
                            return field;
                        });

                        if (fieldsChanged) {
                            updates.fields = JSON.stringify(updatedFields);
                            needsUpdate = true;
                        }
                    } catch (e) {
                        logger.warn(`[SchemaEngine] Failed to parse fields for ${tableName}: ${e.message}`);
                    }

                    if (needsUpdate) {
                        await this.knex(this.systemTableName).where('name', tableName).update(updates);
                        updatedCount++;
                        logger.info(`[SchemaEngine] Updated schema for: ${tableName}`);
                    }
                }
            }
            return { synced: newCount, updated: updatedCount, total: tableNames.length };
        } catch (error) {
            logger.error(`[SchemaEngine] Sync failed: ${error.message}`);
            throw error;
        }
    }

    /**
     * 同步案件数据库中的表
     * @param {Object} caseKnex - 案件数据库 Knex 实例
     * @param {number} caseId - 案件ID
     */
    async syncCaseTables(caseKnex, caseId) {
        try {
            const tables = await caseKnex.raw(
                "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
            );
            const tableNames = tables.map(t => t.name).filter(name => !this.internalTables.includes(name));
            logger.info(`[SchemaEngine] Found ${tableNames.length} case tables: ${tableNames.join(', ')}`);

            const synced = [], updated = [];
            for (const tableName of tableNames) {
                try {
                    const schemaName = `case_${tableName}`;
                    const displayName = this._generateDisplayName(tableName);
                    const existing = await this.knex(this.systemTableName).where('name', schemaName).first();

                    logger.info(`[SchemaEngine] Checking case table: ${tableName}, schemaName: ${schemaName}, exists: ${!!existing}`);

                    if (!existing) {
                        const columns = await caseKnex.raw(`PRAGMA table_info('${tableName}')`);
                        const fields = columns.map(col => ({
                            name: col.name,
                            type: this._sqliteTypeToFieldType(col.type),
                            displayName: this._generateFieldDisplayName(col.name),
                            required: col.notnull === 1,
                            primaryKey: col.pk === 1
                        })).filter(f => !['id', 'created_at', 'updated_at'].includes(f.name));

                        await this.knex(this.systemTableName).insert({
                            name: schemaName,
                            display_name: displayName,
                            fields: JSON.stringify(fields),
                            description: `案件数据表: ${tableName}`,
                            is_system: false,
                            db_type: 'case'
                        });
                        synced.push(tableName);
                        logger.info(`[SchemaEngine] Synced new case table: ${tableName}`);
                    } else {
                        // 检查是否需要更新显示名或字段显示名
                        let needsUpdate = false;
                        const updates = { updated_at: this.knex.fn.now() };

                        // 1. 检查表显示名
                        if (existing.display_name !== displayName) {
                            updates.display_name = displayName;
                            needsUpdate = true;
                        }

                        // 2. 检查字段显示名
                        try {
                            const currentFields = typeof existing.fields === 'string' ? JSON.parse(existing.fields) : existing.fields;
                            let fieldsChanged = false;

                            const updatedFields = currentFields.map(field => {
                                const newDisplayName = this._generateFieldDisplayName(field.name);
                                // 只有当当前显示名等于字段名（未修改）且新显示名不同时才更新
                                if (field.displayName === field.name && newDisplayName !== field.name) {
                                    fieldsChanged = true;
                                    return { ...field, displayName: newDisplayName };
                                }
                                return field;
                            });

                            if (fieldsChanged) {
                                updates.fields = JSON.stringify(updatedFields);
                                needsUpdate = true;
                            }
                        } catch (e) {
                            logger.warn(`[SchemaEngine] Failed to parse fields for case table ${tableName}: ${e.message}`);
                        }

                        if (needsUpdate) {
                            await this.knex(this.systemTableName).where('name', schemaName).update(updates);
                            updated.push(displayName);
                            logger.info(`[SchemaEngine] Updated case table schema: ${tableName}`);
                        }
                    }
                } catch (err) {
                    logger.error(`[SchemaEngine] Failed to sync table ${tableName}: ${err.message}`);
                }
            }
            return { synced: synced.length, updated: updated.length, tables: [...synced, ...updated] };
        } catch (error) {
            logger.error(`[SchemaEngine] Case sync failed: ${error.message}`);
            throw error;
        }
    }

    async getSchemas() {
        try {
            const schemas = await this.knex(this.systemTableName).select('*');
            return schemas.map(s => ({
                ...s,
                fields: typeof s.fields === 'string' ? JSON.parse(s.fields) : s.fields
            }));
        } catch (error) {
            logger.error(`[SchemaEngine] Failed to get schemas: ${error.message}`);
            throw error;
        }
    }

    async getSchemaByName(name) {
        try {
            const schema = await this.knex(this.systemTableName).where('name', name).first();
            if (!schema) return null;
            return {
                ...schema,
                fields: typeof schema.fields === 'string' ? JSON.parse(schema.fields) : schema.fields
            };
        } catch (error) {
            logger.error(`[SchemaEngine] Failed to get schema ${name}: ${error.message}`);
            throw error;
        }
    }

    async updateSchema(name, changes) {
        const trx = await this.knex.transaction();
        try {
            const schema = await trx(this.systemTableName).where('name', name).first();
            if (!schema) throw new Error(`Schema ${name} not found`);

            const currentFields = typeof schema.fields === 'string' ? JSON.parse(schema.fields) : schema.fields;
            let updatedFields = [...currentFields];

            // 检查是否是案件表 (case_ 前缀)
            const isCaseTable = name.startsWith('case_') || schema.db_type === 'case';

            if (changes.addFields && changes.addFields.length > 0) {
                if (isCaseTable) {
                    // 案件表：只更新元数据，物理表在案件数据库中，需要单独处理
                    logger.warn(`[SchemaEngine] Case table ${name}: metadata only update (physical table in case DB)`);
                    for (const field of changes.addFields) {
                        updatedFields.push(field);
                    }
                } else {
                    // 主库表：同时更新物理表和元数据
                    const realTableName = name;
                    for (const field of changes.addFields) {
                        await trx.schema.alterTable(realTableName, (table) => {
                            this._addFieldToTable(table, field);
                        });
                        updatedFields.push(field);
                    }
                }
            }

            if (changes.removeFields && changes.removeFields.length > 0) {
                updatedFields = updatedFields.filter(f => !changes.removeFields.includes(f.name));
            }

            if (changes.modifyFields && changes.modifyFields.length > 0) {
                for (const modifiedField of changes.modifyFields) {
                    const index = updatedFields.findIndex(f => f.name === modifiedField.name);
                    if (index !== -1) {
                        // 只更新允许修改的属性，保留原有的 name 和 type (如果未提供)
                        updatedFields[index] = {
                            ...updatedFields[index],
                            displayName: modifiedField.displayName || updatedFields[index].displayName,
                            required: modifiedField.required !== undefined ? modifiedField.required : updatedFields[index].required,
                            primaryKey: modifiedField.primaryKey !== undefined ? modifiedField.primaryKey : updatedFields[index].primaryKey,
                            // 如果提供了 type，也更新元数据（注意：物理表类型不会改变）
                            type: modifiedField.type || updatedFields[index].type
                        };
                    }
                }
            }

            await trx(this.systemTableName).where('name', name).update({
                fields: JSON.stringify(updatedFields),
                display_name: changes.display_name || schema.display_name,
                description: changes.description !== undefined ? changes.description : schema.description,
                updated_at: trx.fn.now()
            });

            await trx.commit();
            return { name, display_name: changes.display_name || schema.display_name, fields: updatedFields };
        } catch (error) {
            await trx.rollback();
            logger.error(`[SchemaEngine] Failed to update schema ${name}: ${error.message}`);
            throw error;
        }
    }

    async createSchema(schemaDef) {
        const trx = await this.knex.transaction();
        try {
            const existing = await trx(this.systemTableName).where('name', schemaDef.name).first();
            if (existing) throw new Error(`Schema ${schemaDef.name} already exists`);

            const tableExists = await trx.schema.hasTable(schemaDef.name);
            if (tableExists) throw new Error(`Table ${schemaDef.name} already exists`);

            const [id] = await trx(this.systemTableName).insert({
                name: schemaDef.name,
                display_name: schemaDef.display_name || schemaDef.name,
                fields: JSON.stringify(schemaDef.fields || []),
                description: schemaDef.description,
                is_system: false,
                db_type: 'main'
            });

            await trx.schema.createTable(schemaDef.name, (table) => {
                table.increments('id').primary();
                table.timestamp('created_at').defaultTo(this.knex.fn.now());
                table.timestamp('updated_at').defaultTo(this.knex.fn.now());
                table.integer('case_id').index();
                (schemaDef.fields || []).forEach(field => this._addFieldToTable(table, field));
            });

            await trx.commit();
            return { id, name: schemaDef.name, display_name: schemaDef.display_name || schemaDef.name, fields: schemaDef.fields || [], is_system: false };
        } catch (error) {
            await trx.rollback();
            logger.error(`[SchemaEngine] Failed to create schema: ${error.message}`);
            throw error;
        }
    }

    async deleteSchema(name, dropTable = false) {
        const trx = await this.knex.transaction();
        try {
            await trx(this.systemTableName).where('name', name).delete();
            if (dropTable) {
                await trx.schema.dropTableIfExists(name);
            }
            await trx.commit();
            return { deleted: true, tableName: name };
        } catch (error) {
            await trx.rollback();
            logger.error(`[SchemaEngine] Failed to delete schema ${name}: ${error.message}`);
            throw error;
        }
    }

    _addFieldToTable(table, field) {
        let col;
        switch ((field.type || 'string').toLowerCase()) {
            case 'text': case 'string': col = table.string(field.name); break;
            case 'integer': case 'int': col = table.integer(field.name); break;
            case 'real': case 'float': case 'number': col = table.float(field.name); break;
            case 'boolean': col = table.boolean(field.name); break;
            case 'date': case 'datetime': col = table.dateTime(field.name); break;
            case 'json': col = table.json(field.name); break;
            default: col = table.string(field.name);
        }
        if (field.required) col.notNullable();
        if (field.unique) col.unique();
    }

    _sqliteTypeToFieldType(sqliteType) {
        const type = (sqliteType || 'TEXT').toUpperCase();
        if (type.includes('INT')) return 'integer';
        if (type.includes('REAL') || type.includes('FLOAT') || type.includes('DOUBLE')) return 'float';
        if (type.includes('BOOL')) return 'boolean';
        if (type.includes('DATE') || type.includes('TIME')) return 'datetime';
        if (type.includes('BLOB') || type.includes('JSON')) return 'json';
        return 'string';
    }

    _generateDisplayName(tableName) {
        const displayNames = {
            'cases': '案件',
            'personal_info': '个人信息',
            'bank_data': '银行流水',
            'bank_transactions': '银行流水',
            'call_records': '通话记录',
            'profile_entities': '人物档案',
            'profile_relationships': '关系图谱',
            'profile_timeline': '时间线',
            'import_logs': '导入日志',
            'analyzer_profiles': '分析配置',
            'global_configs': '全局配置',
            'ai_prompt_templates': 'AI模板',
            'logistics_data': '物流数据',
            'social_data': '社交数据',
            'transaction_records': '交易记录'
        };
        return displayNames[tableName] || tableName;
    }

    _generateFieldDisplayName(fieldName) {
        const fieldMap = {
            // 通用
            'id': 'ID',
            'case_id': '案件ID',
            'created_at': '创建时间',
            'updated_at': '更新时间',
            'row_hash': '数据指纹',
            'extra_data': '扩展数据(JSON)',
            'remarks': '备注',
            'summary': '摘要',

            // 银行流水
            'transaction_time': '交易时间',
            'account_number': '本方账号',
            'account_name': '本方户名',
            'counterparty_account': '对方账号',
            'counterparty_name': '对方户名',
            'opponent_name': '对方户名',
            'opponent_account': '对方账号',
            'transaction_amount': '交易金额(原始)',
            'amount': '交易金额',
            'balance': '余额',
            'balance_after': '交易后余额',
            'transaction_type': '交易类型',
            'debit_credit_flag': '借贷标识',
            'bank_name': '银行名称',
            'transaction_branch_name': '交易网点',
            'customer_name': '客户姓名',
            'card_number': '卡号',

            // 通话记录
            'user_number': '本方号码',
            'phone_number': '本方号码',
            'opp_user_number': '对方号码',
            'other_number': '对方号码',
            'start_time': '通话时间',
            'call_time': '通话时间',
            'duration': '通话时长(秒)',
            'call_type': '通话类型',
            'user_name': '机主姓名',
            'lac_id': '基站LAC',
            'cell_id': '基站CI',
            'location': '归属地/基站位置'
        };
        return fieldMap[fieldName] || fieldName;
    }
}

module.exports = SchemaEngine;
